package edu.ncist.wang.hkdf.authorization.bussiness.blo;

import java.util.List;
import java.util.Map;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpSession;

import edu.ncist.wang.hkdf.authorization.bussiness.bli.MainIndexAble;
import edu.ncist.wang.hkdf.dao.database.DataBaseAccess;
import edu.ncist.wang.hkdf.dao.database.DataManipulationException;

public class MainIndex implements MainIndexAble {
		public List<Map<String, String>> index(HttpServletRequest request, Map map){
			DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
			String  courseId=(String)map.get("COURSEID");
			String  classId=(String)map.get("CLASSID");
			String sql = "select topic_id,topic_name,avg(final_scorer) as avgScorer,max(final_scorer) as maxScorer,min(final_scorer) as minScorer  "+
								  "  FROM(  "+
								  "  SELECT * FROM  "+
								  " (select  job_is_hard.class_id,job_is_hard.course_id,job_is_hard.final_scorer,job_is_hard.job_id,job_is_hard.topic_id,job_is_hard.topic_name  "+
								   " from job_is_hard LEFT OUTER JOIN course  	on job_is_hard.course_id = course.course_id   "+
								   	"where job_is_hard.course_id='"+courseId+"' and course.is_delete=0 and class_id='"+classId+"'   "+
								  "  and job_id=(select job_id from 	job_is_hard  LEFT OUTER JOIN course	on job_is_hard.course_id = course.course_id  	where job_is_hard.course_id='"+courseId+"'   and course.is_delete=0  and class_id='"+classId+"'   	 order by job_id desc  limit 1)   "+
								  "  ) no1  "+
								  "  ORDER BY topic_id) average  GROUP BY topic_id";
			System.out.println(sql);
			List<Map<String, String>>  list=null;
			try{
				list = dba.getMultiRecord(sql);
			}
			catch(DataManipulationException e){
				System.out.println("index data error!");
				e.printStackTrace();
			}
			return list;
		}

		public List<Map<String, String>> totalScorer(HttpServletRequest request, Map map) {
			DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
			String  courseId=(String)map.get("COURSEID");
			String  classId=(String)map.get("CLASSID");
			String sql="select topic_total_value from topic   "+
								" where belong_course_id='"+courseId+"'   "+
								" and belong_job_id=(select job_id from job_is_hard  where course_id='"+courseId+"'   and class_id='"+classId+"'   	 order by job_id desc  limit 1)";
			List<Map<String, String>> list = null;
			List<Map<String, String>>  totalScorer=null;
			try{
				list = dba.getMultiRecord(sql);
				totalScorer=dba.getMultiRecord(sql);
			}
			catch(DataManipulationException e){
				System.out.println("index data error!");
				e.printStackTrace();
			}
			return list;
		}
		
		
		
		public List<Map<String, String>> jobComplete(HttpServletRequest request, Map map){
			DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
			String  courseId=(String)map.get("COURSEID");
			String  classId=(String)map.get("CLASSID");
			String sql = "select COUNT(student_no) as payable,  "+
									" COUNT(submit_overall_status=0 or null)/COUNT(student_no)*100 as 'normal',  "+
									" COUNT(submit_overall_status=1 or null)/COUNT(student_no)*100 as 'supplementary',   "+  
									" COUNT(submit_overall_status=2 or null)/COUNT(student_no)*100 as 'overdue',job_id   "+
									" FROM   student_complete_status_index  "+
									" WHERE course_id='"+courseId+"'   "+
									" and class_id='"+classId+"'   "+
									"and job_id=(select job_id from student_complete_status_index 	where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)";
			List<Map<String, String>>  list=null;
			try{
				list = dba.getMultiRecord(sql);
			}
			catch(DataManipulationException e){
				System.out.println("index data error!");
				e.printStackTrace();
			}
			return list;
		}
		
		
		public List<Map<String, String>> studentIsEffort(HttpServletRequest request, Map map){
			DataBaseAccess dba = (DataBaseAccess)request.getAttribute("AUTHDATACONNECTION");
			String  courseId=(String)map.get("COURSEID");
			String  classId=(String)map.get("CLASSID");
			String sql = " select student_name  "+
									" from student_is_effort,student  "+
									" where student_is_effort.student_id=student.student_id  "+
									 " and course_id='"+courseId+"' and class_id='"+classId+"'   "+
									 " and is_many_times_to_submit='1'   "+
									 " and job_id=(select job_id from student_is_effort 	where course_id='"+courseId+"'   and class_id='"+classId+"'    order by job_id desc  limit 1)";
			List<Map<String, String>>  list=null;
			try{
				list = dba.getMultiRecord(sql);
			}
			catch(DataManipulationException e){
				System.out.println("index data error!");
				e.printStackTrace();
			}
			return list;
		}
}


